

Setting environment for using XAMPP for Windows.

cubes@CUBES-PC c:\xampp
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE `mb127`;
Query OK, 1 row affected (0.06 sec)

mysql> use `mb127`;
Database changed
mysql> SHOW TABLES;
Empty set (0.06 sec)

mysql> CREATE TABLE `users`;
ERROR 1113 (42000): A table must have at least 1 column
mysql> CREATE TABLE `users`(
     `id` INT AUTO_INCREMENT PRIMARY KEY,
     `name` VARCHAR(255),
     `email` VARCHAR(255),
     `password` CHAR(32),
     `added_date` DATETIME,
     `modified_date` TIMESTAMP,
     `status` ENUM('0','1')
     );
Query OK, 0 rows affected (1.81 sec)

mysql> DESC `users`;
+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field         | Type          | Null | Key | Default           | Extra                       |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| id            | int(11)       | NO   | PRI | NULL              | auto_increment              |
| name          | varchar(255)  | YES  |     | NULL              |                             |
| email         | varchar(255)  | YES  |     | NULL              |                             |
| password      | char(32)      | YES  |     | NULL              |                             |
| added_date    | datetime      | YES  |     | NULL              |                             |
| modified_date | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| status        | enum('0','1') | YES  |     | NULL              |                             |
+---------------+---------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.03 sec)

mysql> DESCRIBE `users`;
+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field         | Type          | Null | Key | Default           | Extra                       |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| id            | int(11)       | NO   | PRI | NULL              | auto_increment              |
| name          | varchar(255)  | YES  |     | NULL              |                             |
| email         | varchar(255)  | YES  |     | NULL              |                             |
| password      | char(32)      | YES  |     | NULL              |                             |
| added_date    | datetime      | YES  |     | NULL              |                             |
| modified_date | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| status        | enum('0','1') | YES  |     | NULL              |                             |
+---------------+---------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.03 sec)

mysql> INSERT INTO `users` VALUES
     (NULL,
     'Rasmus Lerdorf',
     'rasmus@gmail.com',
     '1q1q@1q1q',
     NOW(),
     NULL,
     '1');
Query OK, 1 row affected (1.73 sec)

mysql> INSERT INTO `users`
     (`name`,`added_date`,`email`,`status`,`password`)
     VALUES(
     'Vikas Dwivedi',
     NOW(),
     'vikas.nice@gmail.com',
     '1',
     'qwerty');
Query OK, 1 row affected (1.79 sec)

mysql> INSERT INTO `users` SET
     `name`='Preeti Arya',
     `email`='preeti@arya.com',
     `password`='danger@123',
     `added_date`=NOW(),
     `status`='1';
Query OK, 1 row affected (1.70 sec)

mysql> SELECT * FROM `users`;
+----+----------------+----------------------+------------+---------------------+---------------------+--------+
| id | name           | email                | password   | added_date          | modified_date       | status |
+----+----------------+----------------------+------------+---------------------+---------------------+--------+
|  1 | Rasmus Lerdorf | rasmus@gmail.com     | 1q1q@1q1q  | 2014-03-29 11:13:52 | 2014-03-29 11:13:52 | 1      |
|  2 | Vikas Dwivedi  | vikas.nice@gmail.com | qwerty     | 2014-03-29 11:16:42 | 2014-03-29 11:16:42 | 1      |
|  3 | Preeti Arya    | preeti@arya.com      | danger@123 | 2014-03-29 11:20:11 | 2014-03-29 11:20:11 | 1      |
+----+----------------+----------------------+------------+---------------------+---------------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT `email` FROM `users`;
+----------------------+
| email                |
+----------------------+
| rasmus@gmail.com     |
| vikas.nice@gmail.com |
| preeti@arya.com      |
+----------------------+
3 rows in set (0.00 sec)

mysql> SELECT COUNT(`email`) FROM `users`;
+----------------+
| COUNT(`email`) |
+----------------+
|              3 |
+----------------+
1 row in set (1.68 sec)

mysql> SELECT * FROM `users`;
+----+----------------+----------------------+------------+---------------------+---------------------+--------+
| id | name           | email                | password   | added_date          | modified_date       | status |
+----+----------------+----------------------+------------+---------------------+---------------------+--------+
|  1 | Rasmus Lerdorf | rasmus@gmail.com     | 1q1q@1q1q  | 2014-03-29 11:13:52 | 2014-03-29 11:13:52 | 1      |
|  2 | Vikas Dwivedi  | vikas.nice@gmail.com | qwerty     | 2014-03-29 11:16:42 | 2014-03-29 11:16:42 | 1      |
|  3 | Preeti Arya    | preeti@arya.com      | danger@123 | 2014-03-29 11:20:11 | 2014-03-29 11:20:11 | 1      |
+----+----------------+----------------------+------------+---------------------+---------------------+--------+
3 rows in set (0.00 sec)

mysql> UPDATE `users` SET `password`='pass@123' ;
Query OK, 3 rows affected (1.73 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM `users`;
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
| id | name           | email                | password | added_date          | modified_date       | status |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
|  1 | Rasmus Lerdorf | rasmus@gmail.com     | pass@123 | 2014-03-29 11:13:52 | 2014-03-29 11:23:55 | 1      |
|  2 | Vikas Dwivedi  | vikas.nice@gmail.com | pass@123 | 2014-03-29 11:16:42 | 2014-03-29 11:23:55 | 1      |
|  3 | Preeti Arya    | preeti@arya.com      | pass@123 | 2014-03-29 11:20:11 | 2014-03-29 11:23:55 | 1      |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
3 rows in set (0.00 sec)

mysql> UPDATE `users` SET `password`='abcd'
     WHERE
     `id`='1';
Query OK, 1 row affected (0.16 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE `users` SET `password`='pti'
     WHERE
     `name`='Preeti Arya';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `users`;
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
| id | name           | email                | password | added_date          | modified_date       | status |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
|  1 | Rasmus Lerdorf | rasmus@gmail.com     | abcd     | 2014-03-29 11:13:52 | 2014-03-29 11:24:39 | 1      |
|  2 | Vikas Dwivedi  | vikas.nice@gmail.com | pass@123 | 2014-03-29 11:16:42 | 2014-03-29 11:23:55 | 1      |
|  3 | Preeti Arya    | preeti@arya.com      | pti      | 2014-03-29 11:20:11 | 2014-03-29 11:26:02 | 1      |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
3 rows in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVEPOINT p1;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `users`
     WHERE
     `id`='3';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM `users`;
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
| id | name           | email                | password | added_date          | modified_date       | status |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
|  1 | Rasmus Lerdorf | rasmus@gmail.com     | abcd     | 2014-03-29 11:13:52 | 2014-03-29 11:24:39 | 1      |
|  2 | Vikas Dwivedi  | vikas.nice@gmail.com | pass@123 | 2014-03-29 11:16:42 | 2014-03-29 11:23:55 | 1      |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
2 rows in set (0.00 sec)

mysql> ROLLBACK to p1;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM `users`;
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
| id | name           | email                | password | added_date          | modified_date       | status |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
|  1 | Rasmus Lerdorf | rasmus@gmail.com     | abcd     | 2014-03-29 11:13:52 | 2014-03-29 11:24:39 | 1      |
|  2 | Vikas Dwivedi  | vikas.nice@gmail.com | pass@123 | 2014-03-29 11:16:42 | 2014-03-29 11:23:55 | 1      |
|  3 | Preeti Arya    | preeti@arya.com      | pti      | 2014-03-29 11:20:11 | 2014-03-29 11:26:02 | 1      |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
3 rows in set (0.01 sec)

mysql> SAVEPOINT p1;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVEPOINT p2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `users`;
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
| id | name           | email                | password | added_date          | modified_date       | status |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
|  1 | Rasmus Lerdorf | rasmus@gmail.com     | abcd     | 2014-03-29 11:13:52 | 2014-03-29 11:24:39 | 1      |
|  2 | Vikas Dwivedi  | vikas.nice@gmail.com | pass@123 | 2014-03-29 11:16:42 | 2014-03-29 11:23:55 | 1      |
|  3 | Preeti Arya    | preeti@arya.com      | pti      | 2014-03-29 11:20:11 | 2014-03-29 11:26:02 | 1      |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
3 rows in set (0.00 sec)

mysql> DELETE FROM `users`;
Query OK, 3 rows affected (0.00 sec)

mysql> SELECT * FROM `users`;
Empty set (0.00 sec)

mysql> ROLLBACK to p2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `users`;
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
| id | name           | email                | password | added_date          | modified_date       | status |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
|  1 | Rasmus Lerdorf | rasmus@gmail.com     | abcd     | 2014-03-29 11:13:52 | 2014-03-29 11:24:39 | 1      |
|  2 | Vikas Dwivedi  | vikas.nice@gmail.com | pass@123 | 2014-03-29 11:16:42 | 2014-03-29 11:23:55 | 1      |
|  3 | Preeti Arya    | preeti@arya.com      | pti      | 2014-03-29 11:20:11 | 2014-03-29 11:26:02 | 1      |
+----+----------------+----------------------+----------+---------------------+---------------------+--------+
3 rows in set (0.00 sec)

mysql> DELETE FROM `users`;
Query OK, 3 rows affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.08 sec)

mysql> ROLLBACK to p2;
ERROR 1305 (42000): SAVEPOINT p2 does not exist
mysql> DELETE FROM `users`;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `users`;
Empty set (0.00 sec)

mysql>
